﻿using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;
using Htm2Pdf.Common.Interfaces;

namespace Htm2Pdf.Data
{
    public class Database : IDisposable, IDatabase
    {
        private SqlCeConnection _conn = new SqlCeConnection();
        private bool _disposed;


        public Database(string connectionString)
        {
            _conn.ConnectionString = connectionString;
        }

        public void Connect()
        {
            _conn.Open();
        }

        public void Disconnect()
        {
            Dispose();
        }


        public bool FolderExists(string folder)
        {
            string query = "select count(*) from Folders where Name = @folderName";

            SqlCeCommand cmd = new SqlCeCommand(query, _conn);

            SqlCeParameter parameter = new SqlCeParameter("@folderName", folder);
            cmd.Parameters.Add(parameter);
            int cnt = Convert.ToInt32(cmd.ExecuteScalar());

            return cnt > 0;
        }

        public void AddFolder(string folder)
        {
            string query = "insert into Folders (Name, Created) values (@folderName, @created)";

            SqlCeCommand cmd = new SqlCeCommand(query, _conn);

            SqlCeParameter par1 = new SqlCeParameter("@folderName", folder);
            SqlCeParameter par2 = new SqlCeParameter("@created", DateTime.Now);
            cmd.Parameters.AddRange(new [] {par1, par2});
            cmd.ExecuteNonQuery();
        }

        public void RemoveFolder(string folder)
        {
            string query = "delete from Folders where Name = @folderName";

            SqlCeCommand cmd = new SqlCeCommand(query, _conn);

            SqlCeParameter parameter = new SqlCeParameter("@folderName", folder);
            cmd.Parameters.Add(parameter);
            cmd.ExecuteNonQuery();
        }


        public void AddResult(string url, int durationSecs, string status)
        {
            string query =
                "insert into convresults(url, result, durationSecs, eventDate) values (@url, @result, @durationSecs, @eventDate)";

            SqlCeCommand cmd = new SqlCeCommand(query, _conn);

            SqlCeParameter p1 = new SqlCeParameter("@url", url);
            SqlCeParameter p2 = new SqlCeParameter("@result", status);
            SqlCeParameter p3 = new SqlCeParameter("@durationSecs", durationSecs);
            SqlCeParameter p4 = new SqlCeParameter("@eventDate", DateTime.Now);
            
            cmd.Parameters.AddRange(new []{p1, p2, p3, p4});

            cmd.ExecuteNonQuery();

        }


        ~Database()  
        {
           Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }


        private void Dispose(bool disposing)
        {

            if (!_disposed)
            {

                if (disposing)
                {
                    // dispose managed resources
                    _conn.Close();
                }

                //unmanaged resources...
            }
            _disposed = true;
        }
    }
}
